﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
   public class MajorDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoMajor model)
        {
            model.MAJ_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Major] ");
            sql.Append("([MAJ_ID],[MAJ_ACADEMY],[MAJ_CODE],[MAJ_NAME],[MAJ_CLASSIFICATION],[MAJ_SHORT],[MAJ_ENNAME],[MAJ_TEACHER],[MAJ_CONTENT],[MAJ_START],[MAJ_REMARK],[MAJ_IsShow],[MAJ_LOCAL]) ");
            sql.Append("VALUES ");
            sql.Append("(@MAJ_ID,@MAJ_ACADEMY,@MAJ_CODE,@MAJ_NAME,@MAJ_CLASSIFICATION,@MAJ_SHORT,@MAJ_ENNAME,@MAJ_TEACHER,@MAJ_CONTENT,@MAJ_START,@MAJ_REMARK,@MAJ_IsShow,@MAJ_LOCAL)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoMajor model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Major] SET ");
            sql.Append("MAJ_ACADEMY=@MAJ_ACADEMY,MAJ_CODE=@MAJ_CODE,MAJ_NAME=@MAJ_NAME,MAJ_CLASSIFICATION=@MAJ_CLASSIFICATION,MAJ_SHORT=@MAJ_SHORT,MAJ_ENNAME=@MAJ_ENNAME,MAJ_TEACHER=@MAJ_TEACHER,MAJ_CONTENT=@MAJ_CONTENT,MAJ_START=@MAJ_START,MAJ_REMARK=@MAJ_REMARK,MAJ_IsShow=@MAJ_IsShow,MAJ_LOCAL=@MAJ_LOCAL ");
            sql.Append(" WHERE ");
            sql.Append("MAJ_ID=@MAJ_ID");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string mAJ_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Major] SET ");
            sql.Append("MAJ_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("MAJ_ID=@MAJ_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@MAJ_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = mAJ_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
       /// <summary>
        /// 通过学院的id来删除专业
       /// </summary>
       /// <param name="academyId"></param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteMajorByAcademyId(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Major] SET ");
            sql.Append(" MAJ_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append(" MAJ_ACADEMY=@MAJ_ACADEMY");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@MAJ_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        /// <summary>
        /// 获取一条记录 取得某个学院的信息
        /// </summary>
        /// <returns></returns>
        public BoMajor GetModel(string mAJ_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT MAJ_ID,MAJ_ACADEMY,MAJ_CODE,MAJ_NAME,MAJ_CLASSIFICATION,MAJ_SHORT,MAJ_ENNAME,MAJ_TEACHER,MAJ_CONTENT,MAJ_START,MAJ_REMARK,MAJ_IsShow,MAJ_LOCAL,LOC_NAME,ACA_NAME ");
            sql.Append("FROM Major AS M LEFT JOIN Academy AS A ON M.MAJ_ACADEMY=A.ACA_ID LEFT JOIN Local AS L ON M.MAJ_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append("MAJ_ID=@MAJ_ID AND MAJ_IsShow='true'");
            SqlParameter[] pr = new SqlParameter[]
            {
                new SqlParameter("@MAJ_ID",SqlDbType.NVarChar, 50)
            };
            pr[0].Value = mAJ_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), pr);
            IList<BoMajor> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }
        //取得所有学院的信息 不包括假删除的
        public List<BoMajor> GetAllMajors()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT MAJ_ID,MAJ_ACADEMY,MAJ_CODE,MAJ_NAME,MAJ_CLASSIFICATION,MAJ_SHORT,MAJ_ENNAME,MAJ_TEACHER,MAJ_CONTENT,MAJ_START,MAJ_REMARK,MAJ_IsShow,MAJ_LOCAL,LOC_NAME,ACA_NAME ");
            sql.Append("FROM Major AS M LEFT JOIN Academy AS A ON M.MAJ_ACADEMY=A.ACA_ID LEFT JOIN Local AS L ON M.MAJ_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append("MAJ_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            List<BoMajor> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }

          /// <summary>
        /// 得到分页的数据列表
        /// </summary>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlCon">查询条件</param>
        /// <param name="pagesize">每页显示大小</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="records">返回查询到的记录</param>
        /// <returns>返回对象列表</returns>
        public IList<BoMajor> getPageCompetitions(string sqlCon, string orderCon, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT MAJ_ID,MAJ_ACADEMY,MAJ_CODE,MAJ_NAME,MAJ_CLASSIFICATION,MAJ_SHORT,MAJ_ENNAME,MAJ_TEACHER,MAJ_CONTENT,MAJ_START,MAJ_REMARK,MAJ_IsShow,MAJ_LOCAL,LOC_NAME,ACA_NAME ");
            sql.Append("FROM Major AS M LEFT JOIN Academy AS A ON M.MAJ_ACADEMY=A.ACA_ID LEFT JOIN Local AS L ON M.MAJ_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append("MAJ_IsShow='true'");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), orderCon, pagesize, pageIndex, out records);
            IList<BoMajor> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }
        //
        public List<BoMajor> GetMajorsByAcademy(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT MAJ_ID,MAJ_ACADEMY,MAJ_CODE,MAJ_NAME,MAJ_CLASSIFICATION,MAJ_SHORT,MAJ_ENNAME,MAJ_TEACHER,MAJ_CONTENT,MAJ_START,MAJ_REMARK,MAJ_IsShow,MAJ_LOCAL ");
            sql.Append("FROM Major AS M LEFT JOIN Academy AS A ON M.MAJ_ACADEMY=A.ACA_ID ");
            sql.Append(" WHERE ");
            sql.Append("MAJ_ACADEMY=@MAJ_ACADEMY AND MAJ_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@MAJ_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoMajor> list = GetList(table);
            return list;
        }
       /// <summary>
        /// 通过编号来获取专业
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public List<BoMajor> GetMajorsByCode(string Code)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT MAJ_ID,MAJ_ACADEMY,MAJ_CODE,MAJ_NAME,MAJ_CLASSIFICATION,MAJ_SHORT,MAJ_ENNAME,MAJ_TEACHER,MAJ_CONTENT,MAJ_START,MAJ_REMARK,MAJ_IsShow,MAJ_LOCAL ");
            sql.Append("FROM Major AS M LEFT JOIN Academy AS A ON M.MAJ_ACADEMY=A.ACA_ID ");
            sql.Append(" WHERE ");
            sql.Append("MAJ_CODE=@MAJ_CODE AND MAJ_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@MAJ_CODE",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = Code;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoMajor> list = GetBasicList(table);
            return list!=null&&list.Count>0?list:null;
        }

        /// <summary>
        /// 通过学院编号以及专业编号来获取专业信息
        /// </summary>
        /// <param name="MajorCode">专业编号</param>
        /// <param name="AcademyCode">学院编号</param>
        /// <returns>返回专业对象</returns>
        public BoMajor GetMajorByCodeAndAcaCode(string MajorCode, string AcademyCode)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT MAJ_ID,MAJ_ACADEMY,MAJ_CODE,MAJ_NAME,MAJ_CLASSIFICATION,MAJ_SHORT,MAJ_ENNAME,MAJ_TEACHER,MAJ_CONTENT,MAJ_START,MAJ_REMARK,MAJ_IsShow,MAJ_LOCAL ");
            sql.Append("FROM Major AS M LEFT JOIN Academy AS A ON M.MAJ_ACADEMY=A.ACA_ID ");
            sql.Append(" WHERE ");
            sql.Append("MAJ_CODE=@MAJ_CODE AND MAJ_IsShow='true' AND ACA_CODE=@ACA_CODE");
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@MAJ_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_CODE",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = MajorCode;
            parameter[1].Value = AcademyCode;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoMajor> list = GetBasicList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }
       
        


        public List<BoMajor> GetList(DataTable table)
        {
            List<BoMajor> list = new List<BoMajor>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoMajor model = new BoMajor();
                    model.MAJ_ID = (string)table.Rows[i]["MAJ_ID"];
                    model.MAJ_ACADEMY = table.Rows[i]["MAJ_ACADEMY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_ACADEMY"];
                    model.MAJ_CODE = table.Rows[i]["MAJ_CODE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_CODE"];
                    model.MAJ_NAME = table.Rows[i]["MAJ_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_NAME"];
                    model.MAJ_CLASSIFICATION = table.Rows[i]["MAJ_CLASSIFICATION"] == System.DBNull.Value ? "": (string)table.Rows[i]["MAJ_CLASSIFICATION"];
                    model.MAJ_SHORT = table.Rows[i]["MAJ_SHORT"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_SHORT"];
                    model.MAJ_ENNAME = table.Rows[i]["MAJ_ENNAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_ENNAME"];
                    model.MAJ_TEACHER = table.Rows[i]["MAJ_TEACHER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_TEACHER"];
                    model.MAJ_CONTENT = table.Rows[i]["MAJ_CONTENT"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_CONTENT"];
                    model.MAJ_START = table.Rows[i]["MAJ_START"] == System.DBNull.Value ? DateTime.Today : (DateTime)table.Rows[i]["MAJ_START"];
                    model.MAJ_REMARK = table.Rows[i]["MAJ_REMARK"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_REMARK"];
                    model.MAJ_IsShow = table.Rows[i]["MAJ_IsShow"] == System.DBNull.Value ? true : (bool)table.Rows[i]["MAJ_IsShow"];
                    model.MAJ_LOCAL = table.Rows[i]["MAJ_LOCAL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_LOCAL"];
                    model.ACA_NAME=table.Rows[i]["ACA_NAME"]==System.DBNull.Value? "": (string)table.Rows[i]["ACA_NAME"];
                    model.LOC_NAME = table.Rows[i]["LOC_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["LOC_NAME"];

                    list.Add(model);
                }
            }
            return list;
        }

        public List<BoMajor> GetBasicList(DataTable table)
        {
            List<BoMajor> list = new List<BoMajor>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoMajor model = new BoMajor();
                    model.MAJ_ID = (string)table.Rows[i]["MAJ_ID"];
                    model.MAJ_ACADEMY = table.Rows[i]["MAJ_ACADEMY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_ACADEMY"];
                    model.MAJ_CODE = table.Rows[i]["MAJ_CODE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_CODE"];
                    model.MAJ_NAME = table.Rows[i]["MAJ_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_NAME"];
                    model.MAJ_CLASSIFICATION = table.Rows[i]["MAJ_CLASSIFICATION"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_CLASSIFICATION"];
                    model.MAJ_SHORT = table.Rows[i]["MAJ_SHORT"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_SHORT"];
                    model.MAJ_ENNAME = table.Rows[i]["MAJ_ENNAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_ENNAME"];
                    model.MAJ_TEACHER = table.Rows[i]["MAJ_TEACHER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_TEACHER"];
                    model.MAJ_CONTENT = table.Rows[i]["MAJ_CONTENT"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_CONTENT"];
                    model.MAJ_START = table.Rows[i]["MAJ_START"] == System.DBNull.Value ? DateTime.Today : (DateTime)table.Rows[i]["MAJ_START"];
                    model.MAJ_REMARK = table.Rows[i]["MAJ_REMARK"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_REMARK"];
                    model.MAJ_IsShow = table.Rows[i]["MAJ_IsShow"] == System.DBNull.Value ? true : (bool)table.Rows[i]["MAJ_IsShow"];
                    model.MAJ_LOCAL = table.Rows[i]["MAJ_LOCAL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_LOCAL"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoMajor model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@MAJ_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_ACADEMY",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_CLASSIFICATION",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_SHORT",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_ENNAME",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_TEACHER",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_CONTENT",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_START",SqlDbType.DateTime,50),
                new SqlParameter("@MAJ_REMARK",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_LOCAL",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_IsShow",SqlDbType.Bit,2),
                new SqlParameter("@ACA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@LOC_NAME",SqlDbType.NVarChar,50)
            };
            parameters[0].Value = model.MAJ_ID;
            parameters[1].Value = model.MAJ_ACADEMY;
            parameters[2].Value = model.MAJ_CODE;
            parameters[3].Value = model.MAJ_NAME;
            parameters[4].Value = model.MAJ_CLASSIFICATION;
            parameters[5].Value = model.MAJ_SHORT;
            parameters[6].Value = model.MAJ_ENNAME;
            parameters[7].Value = model.MAJ_TEACHER;
            parameters[8].Value = model.MAJ_CONTENT;
            parameters[9].Value = model.MAJ_START;
            parameters[10].Value = model.MAJ_REMARK;
            parameters[11].Value = model.MAJ_LOCAL;
            parameters[12].Value = model.MAJ_IsShow;
            parameters[13].Value = model.ACA_NAME;
            parameters[14].Value = model.LOC_NAME;
            return parameters;
        }
    }
}
